﻿
using Kjs.Report.Common;
using Kjs.Report.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace Kjs.Report.DB
{
    public class PagedHelper
    {
        public static string CreatePagingSql(int _recordCount, int _pageSize, int _pageIndex, string _safeSql, string _orderField)
        {
            int _pageCount = 0;
            return CreatePagingSql(_recordCount, _pageSize, _pageIndex, _safeSql, _orderField, out _pageCount);
        }
        /// <summary>
        /// 获取分页SQL语句，排序字段需要构成唯一记录
        /// </summary>
        /// <param name="_recordCount">记录总数</param>
        /// <param name="_pageSize">每页记录数</param>
        /// <param name="_pageIndex">当前页数</param>
        /// <param name="_safeSql">SQL查询语句</param>
        /// <param name="_orderField">排序字段，多个则用“,”隔开</param>
        /// <returns>分页SQL语句</returns>
        public static string CreatePagingSql(int _recordCount, int _pageSize, int _pageIndex, string _safeSql, string _orderField, out int _pageCount)
        {
            //重新组合排序字段，防止有错误
            string[] arrStrOrders = _orderField.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            StringBuilder sbOriginalOrder = new StringBuilder(); //原排序字段
            StringBuilder sbReverseOrder = new StringBuilder(); //与原排序字段相反，用于分页
            for (int i = 0; i < arrStrOrders.Length; i++)
            {
                arrStrOrders[i] = arrStrOrders[i].Trim();  //去除前后空格
                if (i != 0)
                {
                    sbOriginalOrder.Append(", ");
                    sbReverseOrder.Append(", ");
                }
                sbOriginalOrder.Append(arrStrOrders[i]);

                int index = arrStrOrders[i].IndexOf(" "); //判断是否有升降标识
                if (index > 0)
                {
                    //替换升降标识，分页所需
                    bool flag = arrStrOrders[i].IndexOf(" DESC", StringComparison.OrdinalIgnoreCase) != -1;
                    sbReverseOrder.AppendFormat("{0} {1}", arrStrOrders[i].Remove(index), flag ? "ASC" : "DESC");
                }
                else
                {
                    sbReverseOrder.AppendFormat("{0} DESC", arrStrOrders[i]);
                }
            }

            //计算总页数
            _pageSize = _pageSize == 0 ? _recordCount : _pageSize;
            int pageCount = (_recordCount + _pageSize - 1) / _pageSize;

            //检查当前页数
            if (_pageIndex < 1)
            {
                _pageIndex = 1;
            }
            else if (_pageIndex > pageCount)
            {
                _pageIndex = pageCount;
            }

            StringBuilder sbSql = new StringBuilder();

            sbSql.AppendFormat(" {0} ", _safeSql);
            sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString());
            if (_recordCount > 0)
                sbSql.AppendFormat(" LIMIT {0},{1} ", _pageSize * (_pageIndex - 1), _pageSize);
            else
                sbSql.AppendFormat(" LIMIT {0},{1} ", 0, _pageSize);

            #region MyRegion


            ////第一页时，直接使用TOP n，而不进行分页查询
            //if (_pageIndex == 1)
            //{
            //    sbSql.AppendFormat(" SELECT * FROM ({0}) AS T ", _safeSql);
            //    sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString());
            //    sbSql.AppendFormat(" LIMIT {0} ", _pageSize);
            //}
            ////最后一页时，减少一个TOP
            //else if (_pageIndex == pageCount)
            //{
            //    sbSql.Append(" SELECT * FROM ");
            //    sbSql.Append(" ( ");
            //    sbSql.AppendFormat(" SELECT * FROM ({0}) AS T ", _safeSql);
            //    sbSql.AppendFormat(" ORDER BY {0} ", sbReverseOrder.ToString());
            //    sbSql.AppendFormat(" LIMIT {0} ", _recordCount - _pageSize * (_pageIndex - 1));
            //    sbSql.Append(" ) AS T ");
            //    sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString());
            //}
            ////前半页数时的分页
            //else if (_pageIndex <= (pageCount / 2 + pageCount % 2) + 1)
            //{
            //    sbSql.Append(" SELECT * FROM ");
            //    sbSql.Append(" ( ");
            //    sbSql.AppendFormat(" SELECT * FROM ", _pageSize);
            //    sbSql.Append(" ( ");
            //    sbSql.AppendFormat(" SELECT * FROM ({0}) AS T ", _safeSql);
            //    sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString());
            //    sbSql.AppendFormat(" LIMIT {0} ", _pageSize * _pageIndex);//
            //    sbSql.Append(" ) AS T ");
            //    sbSql.AppendFormat(" ORDER BY {0} ", sbReverseOrder.ToString());
            //    sbSql.AppendFormat(" LIMIT {0} ", _pageSize);//
            //    sbSql.Append(" ) AS T ");
            //    sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString());
            //}
            ////后半页数时的分页
            //else
            //{
            //    sbSql.AppendFormat(" SELECT * FROM ");
            //    sbSql.Append(" ( ");
            //    sbSql.AppendFormat(" SELECT *  FROM ({0}) AS T ", _safeSql);
            //    sbSql.AppendFormat(" ORDER BY {0} ", sbReverseOrder.ToString());
            //    sbSql.AppendFormat(" LIMIT {0} ", ((_recordCount % _pageSize) + _pageSize * (pageCount - _pageIndex)));//
            //    sbSql.Append(" ) AS T ");
            //    sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString());
            //    sbSql.AppendFormat(" LIMIT {0} ", _pageSize);//
            //}

            #endregion

            _pageCount = pageCount;
            return sbSql.ToString();
        }

        /// <summary>
        /// 获取记录总数SQL语句
        /// </summary>
        /// <param name="_n">限定记录数</param>
        /// <param name="_safeSql">SQL查询语句</param>
        /// <returns>记录总数SQL语句</returns>
        public static string CreateTopnSql(int _n, string _safeSql)
        {
            return string.Format(" SELECT * FROM ({0}) AS T   LIMIT {1} ", _safeSql, _n);
        }

        /// <summary>
        /// 获取记录总数SQL语句
        /// </summary>
        /// <param name="_safeSql">SQL查询语句</param>
        /// <returns>记录总数SQL语句</returns>
        public static string CreateCountingSql(string _safeSql)
        {
            //string start = "from";
            //return "select count(1) " + _safeSql.Substring(_safeSql.ToLower().IndexOf(start.ToLower()), _safeSql.Length - _safeSql.ToLower().IndexOf(start.ToLower()));
            return string.Format(" SELECT COUNT(1) AS RecordCount FROM ({0}) AS T ", _safeSql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="_connectionString"></param>
        /// <param name="_strSql"></param>
        /// <returns></returns>
        public static IList<T> GetList<T>(string _connectionString, string _strSql)
     where T : class
        {
            DataTable dt = DbHelperMySQL.Table(_connectionString, _strSql);
            return DataTableHelper.DataTableToList<T>(dt);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="_connectionString"></param>
        /// <param name="_strSql"></param>
        /// <param name="_limitNum"></param>
        /// <param name="_orderField"></param>
        /// <returns></returns>
        public static IList<T> GetList<T>(string _connectionString, string _strSql,int _limitNum, string _orderField)
where T : class
        {
            //重新组合排序字段，防止有错误
            string[] arrStrOrders = _orderField.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            StringBuilder sbOriginalOrder = new StringBuilder(); //原排序字段
            StringBuilder sbReverseOrder = new StringBuilder(); //与原排序字段相反，用于分页
            for (int i = 0; i < arrStrOrders.Length; i++)
            {
                arrStrOrders[i] = arrStrOrders[i].Trim();  //去除前后空格
                if (i != 0)
                {
                    sbOriginalOrder.Append(", ");
                    sbReverseOrder.Append(", ");
                }
                sbOriginalOrder.Append(arrStrOrders[i]);

                int index = arrStrOrders[i].IndexOf(" "); //判断是否有升降标识
                if (index > 0)
                {
                    //替换升降标识，分页所需
                    bool flag = arrStrOrders[i].IndexOf(" DESC", StringComparison.OrdinalIgnoreCase) != -1;
                    sbReverseOrder.AppendFormat("{0} {1}", arrStrOrders[i].Remove(index), flag ? "ASC" : "DESC");
                }
                else
                {
                    sbReverseOrder.AppendFormat("{0} DESC", arrStrOrders[i]);
                }
            }

            StringBuilder sbSql = new StringBuilder();

            sbSql.AppendFormat(" {0} ", _strSql);
            sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString());
            if (_limitNum > 0)
                sbSql.AppendFormat(" LIMIT {0} ", _limitNum);

            DataTable dt = DbHelperMySQL.Table(_connectionString, sbSql.ToString());
            return DataTableHelper.DataTableToList<T>(dt);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="_connectionString"></param>
        /// <param name="_strSql"></param>
        /// <returns></returns>
        public static PagedList<IList<T>> PagedlList<T>(string _connectionString, string _strSql)
     where T : class
        {
            int _recordCount = Convert.ToInt32(DbHelperMySQL.GetSingle(_connectionString, CreateCountingSql(_strSql)));

            DataTable dt = DbHelperMySQL.Table(_connectionString, _strSql);
            return new PagedList<IList<T>>()
            {
                items = DataTableHelper.DataTableToList<T>(dt),
                totalCount = _recordCount
            };
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="_connectionString"></param>
        /// <param name="_strSql"></param>
        /// <param name="_pageSize"></param>
        /// <param name="_pageIndex"></param>
        /// <param name="_filedOrder"></param>
        /// <returns></returns>
        public static PagedList<IList<T>> PagedList<T>( string _strSql, int _pageSize, int _pageIndex, string _filedOrder)
            where T : class
        {
            _pageSize = _pageSize <= 0 ? 20 : _pageSize;
            _pageIndex = _pageIndex <= 0 ? 1 : _pageIndex;

            int _recordCount = Convert.ToInt32(DbHelperMySQL.GetSingle(DbHelperMySQL.connectionString, CreateCountingSql(_strSql)));
            DataTable dt = DbHelperMySQL.Table(DbHelperMySQL.connectionString, CreatePagingSql(_recordCount, _pageSize, _pageIndex, _strSql, _filedOrder));
            return new PagedList<IList<T>>()
            {
                items = DataTableHelper.DataTableToList<T>(dt),
                totalCount = _recordCount
            };
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="_connectionString"></param>
        /// <param name="_strSql"></param>
        /// <param name="_pageSize"></param>
        /// <param name="_pageIndex"></param>
        /// <param name="_filedOrder"></param>
        /// <returns></returns>
        public static PagedList<IList<T>> PagedlList<T>(string _connectionString, string _strSql, int _pageSize, int _pageIndex, string _filedOrder)
            where T : class
        {
            int _recordCount = Convert.ToInt32(DbHelperMySQL.GetSingle(_connectionString, CreateCountingSql(_strSql)));
            DataTable dt = DbHelperMySQL.Table(_connectionString, CreatePagingSql(_recordCount, _pageSize, _pageIndex, _strSql, _filedOrder));
            return new PagedList<IList<T>>()
            {
                items = DataTableHelper.DataTableToList<T>(dt),
                totalCount = _recordCount
            };
        }
    }

}
